Julia数据科学系列-DataFrames包

# 手动构造: 单个元素的列会广播
DataFrame(A=1:3, B=5:7, fied=1)
# 用 => 进行复杂列名DF的构造:
DataFrame("customer age" => [15, 20, 25],
          "first name" => ["A", "B", "C"])

# 也可从"列名"=>"列值"字典/具名元组中构造DF

# 当列名时, Symbols 快过 Strings

# 从矩阵构造DF:
DataFrame(matrix, names) # names 可以设成`:auto`, 则自动分配列名x1, x2 ..., 或者设成与数据列数相同的数组。

# 按列构造:
df = DataFrame()
df.A = 1:8
df.B = ["M", "F", "F", "M", "F", "M", "M", "F"]

# 按行构造:
df = DataFrame(A=Int[], B=String[])
push!(df, (1, "M"))
push!(df, (2, "N"))

# 从其他`Table.jl`的具象格式中转成DF:
df = sqltable |> DataFrame
df = [(a=1, b=2), (a=3, b=4)] |> DataFrame

# 从CSV文件中读取DF:
using CSV
data = CSV.read(path, DataFrame)

# copy
data2 = copy(data1)

# 列
german.Sex
german."Sex"
german[!, :Sex]
german[!, "Sex"]
names(german)
names(german, AbstractString) # 获取指定类型列的名字
propertynames(german) # 获取Symbols格式的名字
eachcol(german) # 按列迭代
empty, empty!   # 删除行, 但保留列数(维度)

# DataFrame基本信息
size(df)   # (行, 列)
size(df,1) # 第一维的大小(行数)
size(df,2) # 第二维的大小(列数)
nrow(df), ncol(df)
describe(df) # 基本统计信息mean, min, max, median, nmissing, elementType
describe(df, cols=1:3) # 只统计前三列
show(df, allcols=true)
show(df, allrows=true)

mapcols(func, df) # 按列应用func, 返回新df
mapcols(id -> id .^ 2, german)

first(df, n), last(df, n) # n默认是1

df[1:3, :]
df[:, [:A, :B]]
df[!, [:A]] # 返回一个df
df[!, :A] # 返回一个Vector

# 正则; Not; Between; All; Cols
df[!, r"x"] # 取出列名包含x的列
df[!, Not(:x1)] # 取出除了`x1`之外的列
df[!, Between(:x1, :x4)]
df[:, All()]
df[:, Cols(x -> startswith(x, "x"))]
# Cols 还可用于给列重排序:
df[:, Cols(r"x", :)] # 把所有包含x的列排在前面, 其他列排在后边
df[:, Cols(Not(r"x"), :)] # 把所有包含x的列排在后面, 其他列排在前边

# 按照数值过滤
df[df.A .> 500, :] #取出所有A列数值大于 500 的行
df[(df.A .> 500) .& (300 .< df.C .< 400), :]
df[in.(df.A, Ref([1, 5, 601])), :]

subset(df, :A => a -> a .< 10, :C => c -> isodd.(c))

# 用coalesce函数跳过包含missing的行
# coalesce(x...) 返回第一个非missing的值
subset(df, :x => x -> coalesce.(iseven.(x), false)) # 如果是missing, 过滤条件就返回false
# 等价于
subset(df, :x => x -> iseven.(x), skipmissing=true)

julia> df = DataFrame(x1=[1, 2], x2=[3, 4], y=[5, 6])
2×3 DataFrame
 Row │ x1     x2     y
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     1      3      5
   2 │     2      4      6

julia> transform(df, All() => +)
2×4 DataFrame
 Row │ x1     x2     y      x1_x2_y_+
     │ Int64  Int64  Int64  Int64
─────┼────────────────────────────────
   1 │     1      3      5          9
   2 │     2      4      6         12

julia> using Statistics

julia> df = DataFrame(x=[1, 2, missing], y=[1, missing, missing])
3×2 DataFrame
 Row │ x        y
     │ Int64    Int64 
─────┼──────────────────
   1 │       1        1
   2 │       2  missing
   3 │ missing  missing

julia> transform(df, AsTable(:) .=>
                    ByRow.([sum∘skipmissing,
                            x -> count(!ismissing, x),
                            mean∘skipmissing]) .=>
                    [:sum, :n, :mean])
3×5 DataFrame
 Row │ x        y        sum    n      mean
     │ Int64    Int64    Int64  Int64  Float64
─────┼─────────────────────────────────────────
   1 │       1        1      2      2      1.0
   2 │       2  missing      2      1      2.0
   3 │ missing  missing      0      0      NaN

innerjoin(df1, df2, on = :ID) # 大部分join都用同一种语法
innerjoin(df1, df2, on = :ID_df1 => :ID_df2) # 如果要合并的列名字不一样, 用`=>`指示对应关系
crossjoin(df1, df2, makeunique = true) # crossjoin不使用`on`关键词

using DataFras, CSV, Statistics

fpath = joinpath(dirname(pathof(DataFrames)), "..", "docs", "src", "assets", "iris.csv");
iris = CSV.read(fpath, DataFrame)
gdf = groupby(iris, :Species)

combine(gdf, :PetalLength => mean)
combine(gdf, nrow)
combine(gdf, nrow, :PetalLength => mean => :mean)
combine(gdf, [:PetalLength, :SepalLength] => ((p, s) -> (a=mean(p)/mean(s), b=sum(p))),
        AsTable)
combine(gdf, AsTable([:PetalLength, :SepalLength]) =>
        x -> std(x.PetalLength)/std(x.SepalLength))
combine(x -> std(x.PetalLength) / std(x.SepalLength), gdf)
combine(gdf, 1:2 => cor, nrow)
combine(gdf, :PetalLength => (x -> [extrema(x)]) => [:min, :max])

select(gdf, 1:2 => cor)
transform(gdf, :Species => x -> chop.(x, head=5, tail=0))

# do block is supported, but sould be avoided because it is slow:
combine(gdf) do df
   (m = mean(df.PetalLength), s² = var(df.PetalLength))
end

for subdf in groupby(iris, :Species)
    println(size(subdf, 1))
end

for (key, subdf) in pairs(groupby(iris, :Species))
    println("Number of data points for $(key.Species): $(nrow(subdf))")
end

sort!(iris) # 每列都逐级参与排序
sort!(iris, rev = true)
sort!(iris, [:Species, :SepalWidth]) # 指定排序的列
sort!(iris, [order(:Species, by=length), order(:SepalLength, rev=true)]) # 指定排序方式
sort!(iris, [:Species, :PetalLength], rev=[true, false]) # 另一种指定排序方式的语法

df = DataFrame(x = [1, 1, 2, 2], y = [1, 2, 101, 102]);
gdf = groupby(df, :x);
@select(df, :x, :y)
@select(df, :x2 = 2 * :x, :y)
@select(gdf, :x2 = 2 .* :y .* first(:y))
@select!(df, :x, :y)
@select!(df, :x = 2 * :x, :y)
@select!(gdf, :y = 2 .* :y .* first(:y))

@transform(df, :x2 = 2 * :x, :y)
@transform(gdf, :x2 = 2 .* :y .* first(:y))

using Statistics
outside_var = 1;
@subset(df, :x .> 1)
@subset(df, :x .> outside_var)
@subset(df, :x .> outside_var, :y .< 102) # 两个条件是 and 的关系
@subset(gdf, :x .> mean(:x))

@combine(gdf, :x2 = sum(:y))
@combine(gd, :x2 = :y .- sum(:y))
@combine(gd, $AsTable = (n1 = sum(:y), n2 = first(:y)))

@orderby(df, -1 .* :x)
@orderby(df, :x, :y .- mean(:y))

df = DataFrame(x = 1:3, y = [2, 1, 2])
x = [2, 1, 0]

@with(df, :y .+ 1)
@with(df, :x + x)

x = @with df begin
    res = 0.0
    for i in 1:length(:x)
        res += :x[i] * :y[i]
    end
    res
end

# 用^()包裹的Symbols不会被展开成数组
@with(df, df[:x .> 1, ^(:y)])
# 上边这个结果等价于:
df[[1,2,3] .> 1, :y]
# :x 被展开了, :y 没有

df = DataFrame(A = 1:3, B = [2, 1, 2])
df2 = @eachrow df begin
    :A = :B + 1
end

df = DataFrame(A = 1:3, B = [2, 1, 2], C = [-4,2,1])

# 用let包裹, 让x成为局部变量
let x = 0.0
    @eachrow df begin
        if :A < :B
            x += :A * :C
        end
    end
    x
end # x = -4.0

# y是全局变量, 在@eachrow内要用global关键字
y = 0.0
@eachrow df begin
    if :A < :B
        global y += :A * :C
    end
end;
y # y = -4.0

@subset df @byrow begin
    :a > 1 
    :b < 5
end

#以下代码是等价的:
@transform df @byrow @passmissing :c = f(:a, :b)
transform(df, [:a, :b] => ByRow(passmissing(f)) => :c)

julia> no_missing(x::Int, y::Int) = x + y;

julia> df = DataFrame(a = [1, 2, missing], b = [4, 5, 6])
3×2 DataFrame
 Row │ a        b
     │ Int64?   Int64
─────┼────────────────
   1 │       1      4
   2 │       2      5
   3 │ missing      6

julia> @transform df @passmissing @byrow :c = no_missing(:a, :b)
3×3 DataFrame
 Row │ a        b      c
     │ Int64?   Int64  Int64?
─────┼─────────────────────────
   1 │       1      4        5
   2 │       2      5        7
   3 │ missing      6  missing

julia> df = DataFrame(x_str = ["1", "2", missing])
3×1 DataFrame
 Row │ x_str
     │ String?
─────┼─────────
   1 │ 1
   2 │ 2
   3 │ missing

julia> @rtransform df @passmissing :x = parse(Int, :x_str)
3×2 DataFrame
 Row │ x_str    x
     │ String?  Int64?
─────┼──────────────────
   1 │ 1              1
   2 │ 2              2
   3 │ missing  missing

df = DataFrame(A = 1:3, B = [2, 1, 2])

nameA = :A
nameA_string = "A"
df2 = @transform(df, :C = :B - $nameA)
df2 = @transform(df, :C = :B - $nameA_string)
df2 = @transform(df, :C = :B - $"A")
df2 = @transform(df, :C = :B - $:A)

df = DataFrame(A = 1:3, B = [2, 1, 2])

newcol = "C"
@select(df, $newcol = :A + :B)

@by(df, :B, $("A complicated" * " new name") = first(:A))

nameC = "C"
df3 = @eachrow df begin
    @newcol $nameC::Vector{Int}
    $nameC = :A
end

@transform(df, :y = $1 + $2) # 正常
@transform(df, :y = :A + $2) # 报错

# 本质上是因为DataFrame在`source => fun => dest`表达式中, 要求`source`必须是同一种类型:
transform(df, [:A, :B] => (+) => :y)  # 正常
transform(df, [:A, "B"] => (+) => :y) # 报错

using Statistics

df = DataFrame(a = [1, 2], b = [30, 40]);
@transform df $([:a, :b] .=> [sum mean])

# 也可以通过变量传递:
my_transformation = :a => (t -> t .+ 100) => :c;
@transform df begin
    $my_transformation
    :d = :b .+ 200
end

select(df, [:a, :b])
@select df $[:a, :b]

select(df, r"^a")
@select df $(r"^a")

df =DataFrame(x = [1, 1, 2, 2], y = [1, 2, 101, 102]);
@select(df, :x2 = :x, :x3 = ^(:x))

using Statistics 

df = DataFrame(a = repeat(1:5, outer = 20),
               b = repeat(["a", "b", "c", "d"], inner = 25),
               x = repeat(1:20, inner = 5))

x_thread = @chain df begin
    @transform(:y = 10 * :x)
    @subset(:a .> 2)
    @by(:b, :meanX = mean(:x), :meanY = mean(:y))
    @orderby(:meanX)
    @select(:meanX, :meanY, :var = :b)
end

# Get the sum of all columns after 
# a few transformations
@chain df begin 
    @transform(:y = 10 .* :x)
    @subset(:a .> 2)
    @select(:a, :y, :x)
    reduce(+, eachcol(_))
end

# @aside 宏用以临时跳出管道, 也是Chain.jl中的
@chain df begin 
    @transform :y = 10 .* :x
    @aside y_mean = mean(_.y)
    @select :y_standardize = :y .- y_mean
end